Experimental Study of a Self-Tuning Algorithm for DBMS Buffer Pools

The tasks of configuring and tuning large database management systems (DBMSs) have always been both complex and time-consuming. They require knowledge of the characteristics of the system, the data, and the workload, and of the interrelationships between them. The increasing diversity of the data and the workloads handled by today’s systems is making manual tuning by database administrators almost impossible. Self-tuning DBMSs, which dynamically reallocate resources in response to changes in their workload in order to maintain predefined levels of performance, are one approach to handling the tuning problem. In this paper, we apply selftuning technology to managing the buffer pools, which are a key resource in a DBMS. Tuning the size of the buffer pools to a workload is crucial to achieving good performance. We describe a Buffer Pool Tuning Wizard that can be used by database administrators to determine effective buffer pool sizes. The wizard is based on a self-tuning algorithm called the Dynamic Reconfiguration algorithm (DRF), which uses the principle of goal-oriented resource management. It is an iterative algorithm that uses greedy heuristics to find a reallocation that benefits a target transaction class. We define and motivate the cost estimate equations used in the algorithm. We present the results of a set of experiments to investigate the performance of the algorithm.